package impl;

import dao.UserDao;
import model.User;
import util.C3P0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public boolean addUser(User user) throws SQLException {
        Connection conn = null;
        //当前要添加的用户已存在
        if(queryUser(user)!=null) return false;
        int flag = 0;
        try {
            conn = C3P0Util.getConnection();

            String sql = "INSERT INTO t_personnel ( name , password , limit_type) VALUES (? , ? , ?)";

            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1,user.getName());
            ps.setString(2,user.getPassword());
            ps.setInt(3,user.getLimit_type());

            flag = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw e;
        }finally {
        	C3P0Util.close(conn);
        }
        return flag==0?false:true;
    }

    @Override
    public boolean deleteUser(User user) throws SQLException {
        Connection conn = null;

        int flag = 0;
        try {
            conn = C3P0Util.getConnection();

            String sql = "delete from t_personnel where id=?";

            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1,user.getId());

            flag = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw e;
        }finally {
        	C3P0Util.close(conn);
        }
        return flag==0?false:true;
    }

    @Override
    public boolean updateUser(User user) throws SQLException {
        Connection conn = null;
        //当前要修改的已存在
        if(queryUser(user)!=null) return false;
        int flag = 0;
        try {
            conn = C3P0Util.getConnection();

            String sql = "Update t_personnel set name=? , limit_type=? ,password=? where id=?";

            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1,user.getName());
            ps.setInt(2,user.getLimit_type());
            ps.setString(3,user.getPassword());
            ps.setInt(4,user.getId());

            flag = ps.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw e;
        }finally {
        	C3P0Util.close(conn);
        }
        return flag==0?false:true;
    }

    @Override
    public User queryUser(User user) throws SQLException {
        Connection conn = null;
        User u = null;
        try {
            conn = C3P0Util.getConnection();

            String sql = "SELECT * t_personnel where name=? and password=?";

            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1,user.getName());
            ps.setInt(2,user.getId());

            ResultSet rs = ps.executeQuery();

            if(rs.next())
            {
                u = new User();
                u.setId(rs.getInt("id"));
                u.setLimit_type(rs.getInt("limit_type"));
                u.setName(rs.getString("name"));
                u.setPassword(rs.getString("password"));
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw e;
        }finally {
        	C3P0Util.close(conn);
        }
        return u;
    }

    @Override
    public List<User> queryAll(Integer pageNum, Integer pageRow) throws SQLException {
        Connection conn = null;
        List<User> list = new ArrayList<User>();
        User u = null;
        try {
            conn = C3P0Util.getConnection();
            String sql = " select  * from t_personnel order by id asc limit ?, ?";

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, (pageNum - 1) * pageRow);
            ps.setInt(2, pageRow);

            ResultSet rs = ps.executeQuery();

            if(rs.next())
            {
                u = new User();
                u.setId(rs.getInt("id"));
                u.setLimit_type(rs.getInt("limit_type"));
                u.setName(rs.getString("name"));
                u.setPassword(rs.getString("password"));
                list.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }finally{
        	C3P0Util.close(conn);
        }
        return list;

    }
}
